import sys
import openpyxl
from PyQt5.QtWidgets import QWidget, QGraphicsObject, QMainWindow, QApplication, QFileDialog, QPushButton, QTextBrowser
from PyQt5 import QtGui, QtCore
from PyQt5.QtWidgets import QFileDialog, QGraphicsObject
from openpyxl.styles import Font, Alignment
import threading


class biaoge(QMainWindow, QGraphicsObject):
    # 定义信号1 作用触发 携带信息为字符串 使用线程必须使用信号辅助 防止线程资源争夺 导致程序卡死崩溃
    signal1 = QtCore.pyqtSignal(str)

    def __init__(self):
        super().__init__()
        self.resize(1000, 800)
        self.setWindowTitle("城市学院表格生成器")
        self.setupUI()
        self.signal1.connect(self.Qtext_A)
        self.filepath = ''
        self.fileName = ''
        self.btn.clicked.connect(self.open_file)
        self.btn2.clicked.connect(lambda: self.thread_j(self.biaoge))

    def setupUI(self):
        # 设置字体控件
        font = QtGui.QFont()
        font.setFamily("Adobe Arabic")
        font.setPointSize(16)

        self.btn = QPushButton(self)
        self.btn.setText("打开文件")
        self.btn.setFont(font)
        self.btn.resize(100, 40)
        self.btn.move(700, 220)

        self.btn2 = QPushButton(self)
        self.btn2.setText("生成表格")
        self.btn2.setFont(font)
        self.btn2.resize(100, 40)
        self.btn2.move(700, 280)

        # 信息显示框2
        self.QTextB = QTextBrowser(self)
        self.QTextB.setText("记录")
        self.QTextB.setFont(font)
        self.QTextB.resize(400, 350)
        self.QTextB.move(100, 150)

    def open_file(self):
        """
        按钮打开文件获取文件名及文件路径
        """
        filename, filetype = QFileDialog.getOpenFileName(None, "选取Excel文件", "C:/Users/Administrator/Desktop",
                                                         "Excel Files (*.xls *.xlsx)")
        self.fashe(f'打开文件路径{filename}')
        # 将全局变量用来存储 文件路径
        self.filepath = filename
        # 将全局变量用来存储 处理以后的文件名
        self.fileName = self.filepath.split('/')[-1].split('.')[0]

    def Qtext_A(self, message):
        self.QTextB.append(message)

    def fashe(self, message):
        self.signal1.emit(message)

    def thread_j(self, massage):
        t = threading.Thread(target=massage, daemon=True)
        t.start()

    def biaoge(self):
        from openpyxl.styles import Font, Alignment
        from openpyxl.styles import Border, Side
        align = Alignment(horizontal="center", vertical="center", wrap_text=True)
        align1 = Alignment(horizontal="left", vertical="center", wrap_text=True)
        font = Font(name="宋体", size=14, bold=True)
        font1 = Font(name="新宋体", size=9, bold=True)
        border = Border(
            left=Side(border_style="thin", color='FF000000'),
            right=Side(border_style="thin", color='FF000000'),
            top=Side(border_style="thin", color='FF000000'),
            bottom=Side(border_style="thin", color='FF000000')
        )
        wb = openpyxl.load_workbook(filename=f'{self.filepath}')
        bg = wb['Sheet1']['A']
        workbook = openpyxl.Workbook()
        for i in bg:
            workbook.create_sheet(f'{i.value}')
            sheet = workbook[f'{i.value}']
            sheet['A1'] = '体  检  总  表'
            sheet['A1'].alignment = align
            sheet['A1'].font = font
            sheet['A1'].border = border
            sheet.merge_cells('A1:W1')

            sheet['A2'] = '公司名称:'
            sheet['A2'].alignment = align1
            sheet['A2'].font = font
            sheet['A2'].border = border
            sheet.merge_cells('A2:W2')

            sheet['A3'] = '体检单位:'
            sheet['A3'].alignment = align1
            sheet['A3'].font = font
            sheet['A3'].border = border
            sheet.merge_cells('A3:W3')

            sheet['A4'] = '受检日期:'
            sheet['A4'].alignment = align1
            sheet['A4'].font = font
            sheet['A4'].border = border
            sheet.merge_cells('A4:W4')

            sheet['A5'] = '总人数'
            sheet['A5'].alignment = align
            sheet['A5'].font = font1
            sheet['A5'].border = border
            sheet.merge_cells('A5:A6')

            sheet['B5'] = '编码'
            sheet['B5'].alignment = align
            sheet['B5'].font = font1
            sheet['B5'].border = border
            sheet.merge_cells('B5:B6')

            sheet['C5'] = '班级'
            sheet['C5'].alignment = align
            sheet['C5'].font = font1
            sheet['C5'].border = border
            sheet.merge_cells('C5:C6')

            sheet['D5'] = '学号'
            sheet['D5'].alignment = align
            sheet['D5'].font = font1
            sheet['D5'].border = border
            sheet.merge_cells('D5:D6')

            sheet['E5'] = '身份证号'
            sheet['E5'].alignment = align
            sheet['E5'].font = font1
            sheet['E5'].border = border
            sheet.merge_cells('E5:E6')

            sheet['F5'] = '姓名'
            sheet['F5'].alignment = align
            sheet['F5'].font = font1
            sheet['F5'].border = border
            sheet.merge_cells('F5:F6')

            sheet['G5'] = '性别'
            sheet['G5'].alignment = align
            sheet['G5'].font = font1
            sheet['G5'].border = border
            sheet.merge_cells('G5:G6')

            sheet['H5'] = '身高'
            sheet['H5'].alignment = align
            sheet['H5'].font = font1
            sheet['H5'].border = border
            sheet.merge_cells('H5:H6')

            sheet['I5'] = '体重'
            sheet['I5'].alignment = align
            sheet['I5'].font = font1
            sheet['I5'].border = border
            sheet.merge_cells('I5:I6')

            sheet['J5'] = '左眼裸视'
            sheet['J5'].alignment = align
            sheet['J5'].font = font1
            sheet['J5'].border = border
            sheet.merge_cells('J5:J6')

            sheet['K5'] = '右眼裸视'
            sheet['K5'].alignment = align
            sheet['K5'].font = font1
            sheet['K5'].border = border
            sheet.merge_cells('K5:K6')

            sheet['L5'] = '辨色力'
            sheet['L5'].alignment = align
            sheet['L5'].font = font1
            sheet['L5'].border = border
            sheet.merge_cells('L5:L6')

            sheet['M5'] = '内外科'
            sheet['M5'].alignment = align
            sheet['M5'].font = font1
            sheet['M5'].border = border
            sheet.merge_cells('M5:M6')

            sheet['N5'] = '肝功二项'
            sheet['N5'].alignment = align
            sheet['N5'].font = font1
            sheet['N5'].border = border
            sheet.merge_cells('N5:O5')

            sheet['N6'] = 'ATL(0-48)'
            sheet['N6'].alignment = align
            sheet['N6'].font = font1
            sheet['N6'].border = border

            sheet['O6'] = 'AST(0-50)'
            sheet['O6'].alignment = align
            sheet['O6'].font = font1
            sheet['O6'].border = border

            sheet['P5'] = '胆功一项'
            sheet['P5'].alignment = align
            sheet['P5'].font = font1
            sheet['P5'].border = border

            sheet['P6'] = 'TBIL(3.42-20.5)'
            sheet['P6'].alignment = align
            sheet['P6'].font = font1
            sheet['P6'].border = border

            sheet['Q5'] = '肾功二项'
            sheet['Q5'].alignment = align
            sheet['Q5'].font = font1
            sheet['Q5'].border = border
            sheet.merge_cells('Q5:R5')

            sheet['Q6'] = 'BUN（2.85-8.2）'
            sheet['Q6'].alignment = align
            sheet['Q6'].font = font1
            sheet['Q6'].border = border

            sheet['R6'] = 'CRE（53-97）'
            sheet['R6'].alignment = align
            sheet['R6'].font = font1
            sheet['R6'].border = border

            sheet['S5'] = '血液常规'
            sheet['S5'].alignment = align
            sheet['S5'].font = font1
            sheet.merge_cells('S5:V5')
            sheet['S5'].border = border

            sheet['S6'] = '白细胞(4-10)'
            sheet['S6'].alignment = align
            sheet['S6'].font = font1
            sheet['S6'].border = border

            sheet['T6'] = '红细胞（﹤3.5）'
            sheet['T6'].alignment = align
            sheet['T6'].font = font1
            sheet['T6'].border = border

            sheet['U6'] = '血红蛋白(﹤110)'
            sheet['U6'].alignment = align
            sheet['U6'].font = font1
            sheet['U6'].border = border

            sheet['V6'] = '血小板(100-300)'
            sheet['V6'].alignment = align
            sheet['V6'].font = font1
            sheet['V6'].border = border

            sheet['W5'] = '胸片'
            sheet['W5'].alignment = align
            sheet['W5'].font = font1
            sheet['W5'].border = border
            sheet.merge_cells('W5:W6')
            sheet.row_dimensions[5].height = 13.5
            sheet.row_dimensions[6].height = 36
            sheet.column_dimensions['A'].width = 9.5
            sheet.column_dimensions['B'].width = 9.5
            sheet.column_dimensions['C'].width = 9.5
            sheet.column_dimensions['D'].width = 9.5
            sheet.column_dimensions['E'].width = 19.5
            sheet.column_dimensions['F'].width = 9.5
            sheet.column_dimensions['G'].width = 9.5
            sheet.column_dimensions['H'].width = 9.5
            sheet.column_dimensions['I'].width = 9.5
            sheet.column_dimensions['J'].width = 9.5
            sheet.column_dimensions['K'].width = 9.5
            sheet.column_dimensions['L'].width = 9.5
            sheet.column_dimensions['M'].width = 9.5
            sheet.column_dimensions['N'].width = 9.5
            sheet.column_dimensions['O'].width = 9.5
            sheet.column_dimensions['P'].width = 9.5
            sheet.column_dimensions['Q'].width = 9.5
            sheet.column_dimensions['R'].width = 9.5
            sheet.column_dimensions['S'].width = 9.5
            sheet.column_dimensions['T'].width = 9.5
            sheet.column_dimensions['U'].width = 9.5
            sheet.column_dimensions['V'].width = 9.5
            sheet.column_dimensions['W'].width = 9.5
            self.fashe(f'创建{i.value}工作表已完成！')
        rb = workbook['Sheet']
        workbook.remove(rb)
        workbook.save(filename=f'./文件/{self.fileName}.xlsx')
        self.fashe('表格处理完毕！可以关闭本程序了！')


if __name__ == '__main__':
    app = QApplication(sys.argv)
    win = biaoge()
    win.show()
    sys.exit(app.exec_())
